Set Up Metadata Import Excel Template

This section guides you through how to create an Excel template to import metadata into Application Control Manager. When setting up your Metadata Import Template, if you do not specify a field for a specific member, that property will use the default option.

File Tabs

Create a new Excel file with two tabs: Tree and Members.

Headers

On the Tree tab, the first rows should be fixed to: 

  • Row 1, Column A: Application

  • Row 2, Column A: XFW_ACM_[Dimension Tree Table Name] (See Import Add-On Staging Tables for the full name.)

  • Row 3, Column A: Replace

  • Row 4: Property name with a prefix of xfText#:[ ] or xflnt#. The order of column names must match the order in the associated Layouts.

NOTE: The property field type can be found in the database columns definition. For example, nvarchar is a Text type.

On the Members tab, the first rows should be fixed to:

  • Row 1, Column A: Application

  • Row 2, Column A: XFW_ACM_[Dimension Member Table Name]

  • Row 3, Column A: Replace

  • Row 4: Property name with a prefix of xfText#:[ ] or xflnt:#. The order of column names must match the order in the associated Layouts.

Named Range

Application Control Manager looks for the Named Range defined in the Excel file to determine what to import. Ensure the ranges cover the entire data set in your file. The two required name ranges are:

  • xftProjectTree: Refers to ranges in the Tree tab

  • xftProjectMembers: Refers to ranges in the Member tab

 

Import Shared Members and Varying Properties

When importing metadata containing shared members or varying properties, ensure that each shared member and varying property combination is separated by different rows. For shared members, make sure all properties are identical.

Example showing a shared member setup in the Tree tab:

Example showing a shared member with varying properties by Time in the Members tab:

Example showing a shared member with varying properties by Scenario type and Time in the Members tab:

NOTE: The Remove Stored Item feature available in User-Initiate requests or the OneStream Dimension Library is not functional with Metadata Import process. To remove a stored item or revert to the property default value for Vary By Scenario Type and Time properties, either manually update in the Dimension Library or use the User-Initiate request process.

You can give the file any name and save it in any folder location. To upload the Excel template, select Metadata File Import from the left navigation panel. Click Import, and the Windows File Explorer browser will display for you to navigate to the file within your selected folder.